package com.turing.post.datatranferpmbs.impl;

import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.datatranferpmbs.util.PMBSMchTradDetail;
import com.turing.post.datatranferpmbs.util.PMBSMchTradDetailDao;

@Stateless(mappedName = "datatranferpmbs.impl.PMBSMchTradDetailDaoImpl")
@Remote
@Local
public class PMBSMchTradDetailDaoImpl implements PMBSMchTradDetailDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public List<PMBSMchTradDetail> getMchTradDetailList(Integer page,
			Integer maxResult) {
		Integer start = page * maxResult;
		Integer end = (page + 1) * maxResult;
		String sql = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM (select * from MchHisTradDetail m order by m.merchantno,m.trandate,m.transeqno) a WHERE ROWNUM <="
				+ end + ")WHERE rn >" + start;
		Query query = _em.createNativeQuery(sql, PMBSMchTradDetail.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PMBSMchTradDetail> getMchTradDetailListByMerchantNoAndTranDate(
			String merchantNo, String tranDate, String tranCode) {
		String sql = "select * from MchHisTradDetail where (?1 is null or merchantNo =?1) and tranDate = to_date('"
				+ tranDate
				+ "','yyyy-mm-dd') and canceltranseqno is null and TranCode=?2 order by merchantNo,tranSeqNo";
		Query query = _em.createNativeQuery(sql, PMBSMchTradDetail.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, tranCode);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PMBSMchTradDetail> getMchTradDetailList(String merchantNo,
			String tranDate) {
		String sql = "select * from MchHisTradDetail where (?1 is null or merchantNo =?1) and tranDate = to_date('"
				+ tranDate + "','yyyy-mm-dd') order by merchantNo,tranSeqNo";
		Query query = _em.createNativeQuery(sql, PMBSMchTradDetail.class);
		query.setParameter(1, merchantNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMchTradDetailTranDateList() {
		String sql = "select to_char(t.trandate,'yyyy-mm-dd') trandate,count(*) from mchhistraddetail t group by t.trandate order by trandate";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMerchantAndTranDateList(String date) {
		String sql = "select distinct(t.merchantno),to_char(trandate,'yyyy-mm-dd') trandate from mchhistraddetail t where t.trandate=to_date('"
				+ date + "','yyyy-mm-dd') order by merchantno";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}
}
